
ALTER          proc sp_create_update_miscellaneous_lot(
 @part_number varchar(50),
 @lot_number varchar(50),
 @vendor_lotnumber varchar(500),
 @purchase_order varchar(20),
 @lot_stage varchar(50), 
 @stage_position varchar(50),
 @vendor_name varchar(50),
 @ps_type varchar(50),
 @ps_type_name varchar(50),
 @receive_date datetime,
 @date_in datetime,
 @date_out datetime,
 @expected_date_out datetime,
 @wafer_qty_in int,
 @wafer_qty_out int,
 @recieve_qty_in int,
 @qty_in int,
 @qty_out int,
 @qty_reject int,
 @qty_balance int,
 @status varchar(50)
 )
 AS
 begin
 
 	
 	declare @start_date datetime
 	declare @end_date datetime
 	declare @date_time datetime
 
 	declare @temp_varchar varchar(20)
 	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 12:00AM'
 	set @start_date = convert(datetime, @temp_varchar)
 	set @temp_varchar = null
 	set @temp_varchar = convert(varchar, current_timestamp, 101)+' 11:59PM' 
 	set @end_date = convert(datetime, @temp_varchar)
 
 	select @date_time = max(Timestamp) from Miscellaneous_Lot_Table where Lot_Number = @lot_number and Part_Number = @part_number and Vendor_name = @vendor_name and ps_type =@ps_type
 
 	if @date_time is not null
 	begin
 		if @date_time between @start_date and @end_Date
 		begin
 			update Miscellaneous_Lot_Table set Part_Number = @part_number, 
 							Lot_Number = @lot_number, 
 							Purchase_Order = @purchase_order, 
 							Lot_Stage = @lot_stage, 
 							Lot_Stage_Position = @stage_position,
 							Vendor_Name = @vendor_name,
 							PS_Type = @ps_type,
 							PS_Type_Name = @ps_type_name,
 							Recieve_Date = @receive_date,
 							Date_In = @date_in,
 							Date_Out = @date_out,
 							Expected_Date_Out = @expected_date_out,
 							Wafer_Qty_In = @wafer_qty_in,
 							Wafer_Qty_Out = @wafer_qty_out,
 							Recieve_Qty_In = @recieve_qty_in,
 							Qty_In = @qty_in,
 							Qty_Out = @qty_out,
 							Qty_Reject = @qty_reject,
 							Qty_Balance = @qty_balance,
 							Status = @status,
 							Timestamp = current_timestamp,
							MLT_Vendor_Lot_Number = @vendor_lotnumber
 				where Lot_Number = @lot_number and Part_Number = @part_number and Vendor_name = @vendor_name 
 				and timeStamp  = (select max(Timestamp) from Miscellaneous_Lot_Table where Lot_Number = @lot_number and Part_Number = @part_number and Vendor_name = @vendor_name) 
 
 
 		end
 		else
 		begin
 			
 			insert into Miscellaneous_Lot_Table( Part_Number, Lot_Number, Purchase_Order, Lot_Stage, Lot_Stage_Position, Vendor_Name,
 			PS_Type, PS_Type_Name, Recieve_Date, Date_In, Date_Out, Expected_Date_Out, Wafer_Qty_In, Wafer_Qty_Out, Recieve_Qty_In, Qty_In, Qty_Out, Qty_Reject, Qty_Balance, Status, [Timestamp],MLT_Vendor_Lot_Number ) 
 			values(@part_number,  @lot_number,  @purchase_order,  @lot_stage,  @stage_position, @vendor_name,
 			@ps_type, @ps_type_name, @receive_date, @date_in,@date_out, @expected_date_out,  @wafer_qty_in, @wafer_qty_out, @recieve_qty_in, @qty_in, @qty_out, @qty_reject, @qty_balance, @status, current_timestamp,@vendor_lotnumber)
 		end
 	end
 	else
 	begin
 			insert into Miscellaneous_Lot_Table( Part_Number, Lot_Number, Purchase_Order, Lot_Stage, Lot_Stage_Position, Vendor_Name, 
 			PS_Type, PS_Type_Name, Recieve_Date, Date_In, Date_Out, Expected_Date_Out, Wafer_Qty_In, Wafer_Qty_Out, Recieve_Qty_In, Qty_In, Qty_Out, Qty_Reject, Qty_Balance, Status, [Timestamp],MLT_Vendor_Lot_Number )
 			values(@part_number,  @lot_number,  @purchase_order,  @lot_stage,  @stage_position, @vendor_name,
 			@ps_type, @ps_type_name, @receive_date, @date_in,@date_out, @expected_date_out,  @wafer_qty_in, @wafer_qty_out, @recieve_qty_in, @qty_in, @qty_out, @qty_reject, @qty_balance, @status, current_timestamp,@vendor_lotnumber)
 	end
 end
